USE [NLKHOnline]
GO

/****** Object:  StoredProcedure [dbo].[SearchChuyenGiaOnlineCount]    Script Date: 3/23/2014 11:18:51 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- 2014-03-22: hien created
-- =============================================

CREATE PROCEDURE [dbo].[SearchChuyenGiaOnlineCount] 
	@hoTen nvarchar(255) = '',
	@tenDonVi nvarchar(255) = '',
	@tenCongTrinh nvarchar(255) = '',
	@linhVucId int = 0,
	@hocHamId int = 0,
	@hocViId int = 0
AS
BEGIN
	select count(*)
from ChuyenGia CG
	left outer join LinhVuc LV on CG.LinhVucId = LV.Id
where 
	((@linhVucId = 0) or (LinhVucId = @linhVucId)) and
	((@hocHamId = 0) or (HocHamId = @hocHamId)) and
	((@hocViId = 0) or (HocViId = @hocViId)) and
	((@hoTen = '') or (HoTen like N'%' + @hoTen + '%')) and
	((@tenDonVi = '') or (TenDonVi like N'%' + @tenDonVi + '%')) and
	((@tenCongTrinh = '') or 
		(CG.Id in 
			(select distinct ChuyenGiaId
			from CongTrinhNghienCuu
			where (TenCongTrinh like N'%' + @tenCongTrinh + '%')
			)
		))
END

GO


